/*
	THIS PROC LOADS ALL THE CANCELLED LINEITEMS
	EXEC sp_cancellation_report_proc '0','0','0','0','0','0','0','0','0','',1,'0','bymonth','xlsreport'
	EXEC sp_cancellation_report_proc '0','0','0','0','0','0','jabil','0','0','',1,'0','byweek','xlreport'

	SO_ENDCUSTOMER_ID ---> end user
	SO.SO_ODM_ID	  ----> END CUSTOMER
*/
alter proc sp_cancellation_report_proc
 
@FROMDATE	VARCHAR(50),
@TODATE		VARCHAR(50),
@CUSTOMERID	VARCHAR(200),
@PRODUCTID	VARCHAR(200),
@REGION		VARCHAR(50),
@REPOFFICE	VARCHAR(50),
@ENDUSER	VARCHAR(500),--parameter for end customer
@SALESPERSON	VARCHAR(500),
@DEFAULTPROMPT	VARCHAR(50),
@BOOKINGTYPE	VARCHAR(50),
@ORDERTYPE	INT,
@ENDCUSTOMER	VARCHAR(500),--paramerter for end user
@BYPERIOD	VARCHAR(50),
@REPORTTYPE	VARCHAR(50)

AS
BEGIN
	CREATE TABLE #RESULTS 
			([LINE ITEM NO] INT, PRODUCT_ID INT, QUANTITY INT, [UNIT PRICE] FLOAT,[EXTENDED PRICE] FLOAT, 
			 [REQUESTED DATE] DATETIME,[REQUESTED DATE WORK WEEK NO] INT,[REQUESTED DATE QUARTER] INT,
			 [SCHEDULE DATE] DATETIME,[SCHEDULE DATE WORK WEEK NO] INT,[SCHEDULE DATE QUARTER] INT,
			 [CANCEL DATE] DATETIME,
			 [PRODUCT NAME] VARCHAR(50), SALES_ORDER_ID INT,[SALES ORDER NUMBER] VARCHAR(50),CUSTOMER_ID INT,
			 SO_CUSTOMER_PO_NUMBER VARCHAR(50),[SALES PERSON] VARCHAR(50), [END CUSTOMER] VARCHAR(65), 
			 [SO BOOKING DATE] DATETIME,[SO BOOKING DATE WORK WEEK NO]INT,[SO BOOKING DATE QUARTER] INT,
			 SO_IS_SAMPLE INT,[CUSTOMER NAME] VARCHAR(65), C_REGION VARCHAR(50), SO_REP_OFFICE VARCHAR(50),
			 C_SALES_PERSON VARCHAR(50),[END USER] VARCHAR(65), PERIODICALVALUE INT,ACTUAL_YEAR int,ISCONSIGNMENT BIT ) 
 
	DECLARE @CANCELSTRING VARCHAR(4000)
	SET @CANCELSTRING = N'
	INSErT INTO #RESULTS
	SELECT  CCOL.CCOL_LINE_ITEM_NO, CCOL.PRODUCT_ID, CCOL.CCOL_QTY, CCOL.CCOL_UNIT_COST, CCOL.CCOL_AMOUNT, 
		CCOL.CCOL_REQUESTED_DATE,CCOL_REQUESTED_DATE_WW_NO,CCOL_REQUESTED_DATE_QTR_NO,
		CCOL.CCOL_SCHEDULE_DATE,CCOL_SCHEDULE_DATE_WW_NO,CCOL_SCHEDULE_DATE_QTR_NO,
		CCOL.[TIMESTAMP], 
		P.PART_NUMBER, SO.SALESORDER_ID, SO.SALESORDER_NO, SO.CUSTOMER_ID, 
		SO.SO_CUSTOMER_PO_NUMBER, SO.SO_SALES_PERSON, 
		CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) 
			THEN C.C_NAME --SO CUSTOMER NAME
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
			END AS ENDUSER,
		SO.SO_BOOKING_DATE,SO_BOOKING_DATE_WW_NO,SO_BOOKING_DATE_QTR_NO, 
		SO.SO_IS_SAMPLE, C.C_NAME, C.C_REGION, SO.REGIONAL_REP, C.C_SALES_PERSON,
		CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) 
			THEN (CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) 
				THEN C.C_NAME --SO CUSTOMER NAME
				ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
				END) 
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
			END AS ENDCUSTOMER,
		(CASE 
			WHEN '''+ @BYPERIOD +''' = ''BYMONTH'' THEN DATEPART( m, CCOL.[TIMESTAMP] )
			WHEN '''+ @BYPERIOD +''' = ''byQuarter'' THEN DATEPART( Q, CCOL.[TIMESTAMP] )
			ELSE DATEPART(wk, CCOL.[TIMESTAMP] )
		END),DATEPART( yyyy, CCOL.[TIMESTAMP] ) as actual_year, SO_IS_CONSIGNMENT
--SELECT *
	FROM CUSTOMER_CANCEL_ORDER_LINEITEM CCOL 
		INNER JOIN SALES_ORDER SO ON CCOL.SALESORDER_ID = SO.SALESORDER_ID 
		INNER JOIN PRODUCT P ON CCOL.PRODUCT_ID = P.PRODUCT_ID 
		INNER JOIN CUSTOMER C ON SO.CUSTOMER_ID = C.CUSTOMER_ID 

	WHERE    1=1  '

	IF @PRODUCTID IS NOT NULL AND @PRODUCTID <> '0'
	BEGIN
		SET @CANCELSTRING = @CANCELSTRING + ' AND CCOL.Product_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PRODUCTID +'''))'
	END
	IF @CUSTOMERID IS NOT NULL AND @CUSTOMERID <> '0'
	BEGIN
		SET @CANCELSTRING = @CANCELSTRING + ' AND SO.Customer_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @CUSTOMERID +'''))'
	END
	IF @REGION IS NOT NULL AND @REGION <> '0'
	BEGIN
		SET @CANCELSTRING = @CANCELSTRING + ' AND C.C_REGION = '''+ @REGION+''''
	END
	IF @REPOFFICE IS NOT NULL AND @REPOFFICE <> '0'
	BEGIN
		SET @CANCELSTRING = @CANCELSTRING + ' AND SO.REGIONAL_REP = '''+ @REPOFFICE+''''
	END
	IF @ENDUSER IS NOT NULL AND @ENDUSER <> '0'
	BEGIN
		SET @CANCELSTRING = @CANCELSTRING + ' AND CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) 
								THEN C.C_NAME --SO CUSTOMER NAME
								ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
							END IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @ENDUSER +'''))'
	END
	IF @SALESPERSON IS NOT NULL AND @SALESPERSON <> '0'
	BEGIN
		SET @CANCELSTRING = @CANCELSTRING + ' AND C.C_SALES_PERSON IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @SALESPERSON +'''))'
	END
	IF @ENDCUSTOMER IS NOT NULL AND @ENDCUSTOMER <> '0'
	BEGIN
		SET @CANCELSTRING = @CANCELSTRING + ' AND CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) 
								THEN (CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) 
									THEN C.C_NAME --SO CUSTOMER NAME
									ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
									END) 
								ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
							END IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @ENDCUSTOMER +'''))'
	END

	IF @ORDERTYPE IS NOT NULL AND @ORDERTYPE <> 2
	BEGIN
		SET @CANCELSTRING = @CANCELSTRING + ' AND SO_IS_CONSIGNMENT = 1 AND SO.SO_Is_Sample = '''+ CONVERT(VARCHAR,@ORDERTYPE)+''''
	END
	ELSE IF @ORDERTYPE IS NOT NULL AND @ORDERTYPE = 2
	BEGIN
		SET @CANCELSTRING = @CANCELSTRING + ' AND SO_IS_CONSIGNMENT = 0 '
	END

	IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' )
	BEGIN
		SET @CANCELSTRING = @CANCELSTRING +' AND CCOL.[TIMESTAMP] >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
	END
	IF ( @TODATE IS NOT NULL AND @TODATE <> '0' )
	BEGIN
		SET @CANCELSTRING = @CANCELSTRING + +' AND CCOL.[TIMESTAMP] <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
	END
	
	--PRINT (@CANCELSTRING)
	EXEC (@CANCELSTRING)
	
	
	IF @REPORTTYPE = 'XLREPORT'
	BEGIN
		SELECT 
			[CUSTOMER NAME],[END CUSTOMER],[END USER],[SALES ORDER NUMBER],[LINE ITEM NO],[SALES PERSON],
			[SO BOOKING DATE],[SO BOOKING DATE WORK WEEK NO],[SO BOOKING DATE QUARTER],
			[REQUESTED DATE],[REQUESTED DATE WORK WEEK NO],[REQUESTED DATE QUARTER],
			[SCHEDULE DATE],[SCHEDULE DATE WORK WEEK NO],[SCHEDULE DATE QUARTER],
			[CANCEL DATE],
			[PRODUCT NAME],QUANTITY,[UNIT PRICE],[EXTENDED PRICE]
		FROM #RESULTS 
		ORDER BY ACTUAL_YEAR,PERIODICALVALUE,[CANCEL DATE] 
	END
	ELSE
	BEGIN
		SELECT  * FROM #RESULTS ORDER BY ACTUAL_YEAR,PERIODICALVALUE,[CANCEL DATE] 
	END
	
end	